﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

using ECS.DAL.Entity;

namespace ECS.DAL.Component
{
    public class WeightRecordComponent : BaseSQLModel
    {
        #region private properties
        private const string SQL_QUERY = " ";

        private const string SQL_PROC_QUERY = " ";

        private const string SQL_PARAM_SEARCH_SETUP_ID = " ";

        private const string SQL_INSERT = " ";

        private const string SQL_PROC_INSERT = "usp_InsertWeightRecord @AppId,  " +
                                    " @SlipId, @WeightNumber, @GrossWeight, @TareWeight, @NetWeight, @PalletCount, " +
                                    " @Device, @UserId "; 

        private const string SQL_UPDATE = " ";

        private const string SQL_PROC_UPDATE = "usp_UpdateWeightRecord ";

        private const string SQL_DELETE = " ";
        #endregion

        #region public properties
        /// <summary>
        /// Error result
        /// </summary>
        public ErrorInfo ErrorMessage { set; get; }
        #endregion

        #region constructor
        public WeightRecordComponent()
        {
            ErrorMessage = new ErrorInfo();
        }
        #endregion

        #region private methods
        
        #endregion

        #region public mehods
        /// <summary>
        /// get weight list by MAWB
        /// </summary>
        /// <param name="mawbId"></param>
        /// <returns></returns>
        public DataSet GetSelectWeight(string mawbId)
        {
            DataSet ds = new DataSet();
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from v_weight where mawb_id = '@mawb_id' ");
            sb.Replace("@mawb_id", mawbId);
            using (Entities entities = new Entities())
            {
                entities.Open();
                ds = entities.GetDataBySQL(sb.ToString(), "dtDimDetail");
            }
            return ds;
        }
        /// <summary>
        /// get Eics setup data to list
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public List<ObjectContext> Search(ObjectContext parameter)
        {
            MAWB param = (MAWB)parameter;
            List<ObjectContext> items = new List<Entity.ObjectContext>();
            //using (Entities entities = new Entities())
            //{
            //    StringBuilder sqlString = new StringBuilder();
            //    sqlString.Append(SQL_PROC_QUERY);
            //    //sqlString.Append(SQL_QUERY);

            //    //if (!string.IsNullOrEmpty(param.SetupId))
            //    //{
            //    //    sqlString.Append(SQL_PARAM_SEARCH_SETUP_ID);
            //    //    sqlString.Replace("@setup_id", BaseSQLModel.SqlWithQoute(param.SetupId));
            //    //}
                
            //    entities.Open();
            //    Console.WriteLine(sqlString.ToString());
            //    DbCommand cmd = entities.CreateCommand(sqlString.ToString());
            //    cmd.CommandType = System.Data.CommandType.StoredProcedure;
            //    cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", param.AppId));
            //    cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", param.Device));
            //    cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", param.UserId));
            //    DbDataReader reader = cmd.ExecuteReader();
            //    while (reader.Read())
            //    {
            //        EicsSetup data = new EicsSetup();
            //        data.SetupId = base.GetString(0, reader);
            //        data.StationId = base.GetString(1, reader);
            //        data.SetupType = base.GetString(2, reader);
            //        data.SettingName = base.GetString(3, reader);
            //        data.ValueString = base.GetString(4, reader);
            //        data.ValueNumber  = (float?)base.GetDouble(5, reader);
            //        data.ValueDate = (DateTime?)base.GetDate(6, reader);
            //        data.CreateDate = (DateTime)base.GetDate(7, reader);
            //        data.CreateBy= base.GetString(8, reader);
            //        data.NotChangeObject();
            //        items.Add(data);
            //    }
            //}
            return items;
        }

        public override string GetInsertSQL(ObjectContext param)
        {
            /*@AppId, @SlipId, @WeightNumber, @GrossWeight, @TareWeight, @NetWeight, @PalletCount, @Device, @UserId "; */
            WeightRecord parameter = (WeightRecord)param;
            StringBuilder sqlString = new StringBuilder();
            //insert command
            sqlString.Append(SQL_PROC_INSERT);
            //sqlString.Replace("@AppId", BaseSQLModel.SqlWithQoute(parameter.AppId));
            //sqlString.Replace("@SlipId", BaseSQLModel.SqlWithQoute(parameter.SlipId));
            //sqlString.Replace("@WeightNumber", BaseSQLModel.SqlWithoutQoute(parameter.WeightNumber));
            //sqlString.Replace("@GrossWeight", BaseSQLModel.SqlWithoutQoute(parameter.GrossWeight));
            //sqlString.Replace("@TareWeight", BaseSQLModel.SqlWithoutQoute(parameter.TareWeight));
            //sqlString.Replace("@NetWeight", BaseSQLModel.SqlWithoutQoute(parameter.NetWeight));
            //sqlString.Replace("@PalletCount", BaseSQLModel.SqlWithoutQoute(parameter.PalletCount));
            //sqlString.Replace("@Device", BaseSQLModel.SqlWithQoute(parameter.Device));
            //sqlString.Replace("@UserId", BaseSQLModel.SqlWithQoute(parameter.UserId));

            return sqlString.ToString();
        }

        public override string GetUpdateSQL(ObjectContext param)
        {
            WeightRecord parameter = (WeightRecord)param;
            StringBuilder sqlString = new StringBuilder();
            //sqlString.Append(SQL_UPDATE);
            sqlString.Append(SQL_PROC_UPDATE);
            //sqlString.Replace("@AppId", BaseSQLModel.SqlWithQoute(parameter.AppId));
            //sqlString.Replace("@setup_id", BaseSQLModel.SqlWithQoute(parameter.SetupId));
            //sqlString.Replace("@station_id", BaseSQLModel.SqlWithQoute(parameter.StationId));
            //sqlString.Replace("@type", BaseSQLModel.SqlWithQoute(parameter.SetupType));
            //sqlString.Replace("@setting_name", BaseSQLModel.SqlWithQoute(parameter.SettingName));
            //sqlString.Replace("@setting_value_string", BaseSQLModel.SqlWithQoute(parameter.ValueString));
            //sqlString.Replace("@setting_value_number", parameter.ValueNumber == null ? "NULL" : parameter.ValueNumber.ToString());
            //sqlString.Replace("@setting_value_datetime", parameter.ValueDate == null ? "NULL" : BaseSQLModel.SqlWithQoute(string.Format("{0:yyyy-MM-dd}", parameter.ValueDate)));
            ////sqlString.Replace("@create_date", BaseSQLModel.SqlWithQoute(parameter.CreateDate.ToString("yyyy-MM-dd")));
            ////sqlString.Replace("@create_by", BaseSQLModel.SqlWithQoute(parameter.CreateBy));
            //sqlString.Replace("@Device", BaseSQLModel.SqlWithQoute(parameter.Device));
            //sqlString.Replace("@UserId", BaseSQLModel.SqlWithQoute(parameter.UserId));

            return sqlString.ToString();
        }

        public override string GetDeleteSQL(ObjectContext param)
        {
            WeightRecord parameter = (WeightRecord)param;
            StringBuilder sqlString = new StringBuilder();
            //insert command
            //sqlString.Append(SQL_DELETE);
            //sqlString.Replace("@setup_id", BaseSQLModel.SqlWithQoute(parameter.SetupId));
            return sqlString.ToString();
        }
        /// <summary>
        /// Insert to data source
        /// </summary>
        /// <param name="param"></param>
        public override void Insert(ObjectContext param)
        {
            DataSet ds = new DataSet();
            WeightRecord parameter = (WeightRecord)param;
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetInsertSQL(param));
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipID", parameter.SlipId));
                    cmd.Parameters.Add(entities.CreateParameter("@in_intWeightNumber", parameter.WeightNumber));
                    cmd.Parameters.Add(entities.CreateParameter("@in_fltGrossWeight", parameter.GrossWeight));
                    cmd.Parameters.Add(entities.CreateParameter("@in_fltTareWeight", parameter.TareWeight));
                    cmd.Parameters.Add(entities.CreateParameter("@in_fltNetWeight", parameter.NetWeight));
                    cmd.Parameters.Add(entities.CreateParameter("@in_intPalletCount", parameter.PalletCount));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                    DbDataAdapter adapter = entities.CreateDataAdapter(GetInsertSQL(param));
                    adapter.SelectCommand = cmd;
                    adapter.Fill(ds, "Result");
                    //entities.SetCommand(cmd);
                    //int m_iRecordChanged = entities.SaveChanges();
                    //if (m_iRecordChanged < 1)
                    if (ds != null) //ds.Tables[0].Rows.Count > 0)
                    {
                        ErrorMessage.Status = ds.Tables[0].Rows[0]["Status"].ToString();
                        ErrorMessage.ErrorCode = ds.Tables[0].Rows[0]["ErrorCode"].ToString();
                        ErrorMessage.Message = ds.Tables[0].Rows[0]["Message"].ToString();
                        ErrorMessage.ID = string.Empty;
                    }
                  
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// update to datasource
        /// </summary>
        /// <param name="param"></param>
        public override void Update(ObjectContext param)
        {
            DataSet ds = new DataSet();
            WeightRecord parameter = (WeightRecord)param;
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetUpdateSQL(param));
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchWeightID", parameter.WeightId));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipID", parameter.SlipId));
                    cmd.Parameters.Add(entities.CreateParameter("@in_intWeightNumber", parameter.WeightNumber));
                    cmd.Parameters.Add(entities.CreateParameter("@in_fltGrossWeight", parameter.GrossWeight));
                    cmd.Parameters.Add(entities.CreateParameter("@in_fltTareWeight", parameter.TareWeight));
                    cmd.Parameters.Add(entities.CreateParameter("@in_fltNetWeight", parameter.NetWeight));
                    cmd.Parameters.Add(entities.CreateParameter("@in_intPalletCount", parameter.PalletCount));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                    DbDataAdapter adapter = entities.CreateDataAdapter(cmd);
                    //adapter.SelectCommand = cmd;
                    adapter.Fill(ds, "Result");
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        ErrorMessage.Status = ds.Tables[0].Rows[0]["Status"].ToString();
                        ErrorMessage.ErrorCode = ds.Tables[0].Rows[0]["ErrorCode"].ToString();
                        ErrorMessage.Message = ds.Tables[0].Rows[0]["Message"].ToString();
                        ErrorMessage.ID = string.Empty;
                        if (!ErrorMessage.Status.Equals("PASS"))
                        {
                            throw new Exception(string.Format("{0}:{1}", ErrorMessage.ErrorCode, ErrorMessage.Message));
                        }
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// delete from datasource
        /// </summary>
        public override void Delete(ObjectContext param)
        {
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetDeleteSQL(param));
                    entities.SetCommand(cmd);
                    int m_iRecordChanged = entities.SaveChanges();
                    if (m_iRecordChanged < 0)
                    {
                        throw new Exception("weight record delete unsuccess.");
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// special case for store procedure to update data that return result as record
        /// </summary>
        /// <param name="items"></param>
        public override void SaveChanges(List<ObjectContext> items)
        {
            string sqlCmd = string.Empty;
            string textMessage = string.Empty;
            WeightRecord parameter = null;
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DataSet ds = new DataSet();
                    DbCommand cmd = entities.CreateCommand(sqlCmd);
                    
                    
                    DbDataAdapter adapter = entities.CreateDataAdapter(sqlCmd);
                    adapter.SelectCommand = cmd;
                    //cmd.CommandType = CommandType.StoredProcedure;
                    foreach (ObjectContext item in items)
                    {
                        parameter = (WeightRecord)item;
                        switch (item.GetObjectSate())
                        {
                            case OBJECT_STATE.Insert:
                                sqlCmd = GetInsertSQL(item);
                                cmd.CommandText = sqlCmd;
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.Clear();
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipID", parameter.SlipId));
                                cmd.Parameters.Add(entities.CreateParameter("@in_intWeightNumber", parameter.WeightNumber));
                                cmd.Parameters.Add(entities.CreateParameter("@in_fltGrossWeight", parameter.GrossWeight));
                                cmd.Parameters.Add(entities.CreateParameter("@in_fltTareWeight", parameter.TareWeight));
                                cmd.Parameters.Add(entities.CreateParameter("@in_fltNetWeight", parameter.NetWeight));
                                cmd.Parameters.Add(entities.CreateParameter("@in_intPalletCount", parameter.PalletCount));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                                textMessage = "Insert ";
                                break;
                            case OBJECT_STATE.Update:
                                sqlCmd = GetUpdateSQL(item);
                                textMessage = "Update ";
                                break;
                            case OBJECT_STATE.Delete:
                                sqlCmd = GetDeleteSQL(item);
                                textMessage = "Delete ";
                                break;
                            default:
                                sqlCmd = string.Empty;
                                break;
                        }
                        if (!string.IsNullOrEmpty(sqlCmd))
                        {
                            //DbCommand cmd = entities.CreateCommand(sqlCmd);
                            //cmd.CommandType = CommandType.StoredProcedure;
                            //entities.SetCommand(cmd);
                            //int m_iRecordChanged = entities.SaveChanges();
                            //int m_iRecordChanged = cmd.ExecuteNonQuery();
                            //if (m_iRecordChanged < 0)
                            //{
                            //    throw new Exception(string.Format("{0} transaction failed", textMessage));
                            //}
                            //cmd.CommandText = sqlCmd;

                            adapter.SelectCommand = cmd;
                            adapter.Fill(ds);
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                if (!ds.Tables[0].Rows[0]["Status"].ToString().Equals("PASS"))
                                {
                                    ErrorMessage.Status = ds.Tables[0].Rows[0]["Status"].ToString();
                                    ErrorMessage.ErrorCode = ds.Tables[0].Rows[0]["ErrorCode"].ToString();
                                    ErrorMessage.Message = ds.Tables[0].Rows[0]["Message"].ToString();
                                }
                            }
                            //item.NotChangeObject();
                        }
                    }
                }
                transaction.Complete();
                items.RemoveAll(o => o.ObjectStatus == OBJECT_STATE.Delete);
                foreach (ObjectContext data in items)
                {
                    data.NotChangeObject();
                }
            }
        }
        #endregion
    }
}
